package me.wz.util.sdbc.dbutils;

import com.google.gson.GsonBuilder;
import me.wz.util.Assert;
import me.wz.util.exception.RTException;
import me.wz.util.sdbc.*;
import me.wz.util.sdbc.OutParameter;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.persistence.Column;
import javax.sql.DataSource;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Sdbc extends TranscationSupport implements ISdbc {

    private DataSource dataSource = null;
    private QueryRunner queryRunner = null;

    public Sdbc(){}
    public Sdbc(DataSource dataSource){ this.setDataSource(dataSource); }
    public Sdbc(QueryRunner queryRunner){ this.setQueryRunner(queryRunner); }

    private IDialect dialect;
    public IDialect getDialect() {
        try {
            if( dialect == null ) {
                synchronized (this) {
                    if( dialect == null ) {
                        Connection connection = getConnection();
                        try {
                            String dbName = connection.getMetaData().getDatabaseProductName();
                            dialect = Dialect.getDialect(dbName);
                            Assert.notNull(dialect, "dialect can't be null." + dbName);
                        }
                        finally {
                            try { if( connection != null ){ connection.close(); } } catch(Exception ex){}// ignore
                        }
                    }
                }
            }
            return dialect;
        }
        catch(Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    public void setDialect(IDialect dialect) { this.dialect = dialect; }

    private String onPrepareStatement(String sql){
        AtomicBoolean isPrepared = new AtomicBoolean(false);
        String ret = Intcpt.onPrepareStatement(sql, isPrepared);
        if( logger.isDebugEnabled() && isPrepared.get() ) {
            logger.debug("[{}]{} => {}", !sql.equals(ret), sql, ret);
        }
        return ret;
    }

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Override
    public <T> List<T> select(T bean) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.select(bean)), bean);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().query(connection, SQLParser.getSql(), newBeanListHandler((Class<T>)bean.getClass()), SQLParser.getValues());
            }
            return getQueryRunner().query(SQLParser.getSql(), newBeanListHandler((Class<T>)bean.getClass()), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> List<T> select(Class<T> clazz) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.select(clazz)), null);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().query(connection, SQLParser.getSql(), newBeanListHandler(clazz), SQLParser.getValues());
            }
            return getQueryRunner().query(SQLParser.getSql(), newBeanListHandler(clazz), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public List<Map<String, Object>> select(String select) { return select(select, null); }
    @Override
    public List<Map<String, Object>> select(String select, Map<String, Object> params) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(select), params);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().query(connection, SQLParser.getSql(), newMapListHandler(), SQLParser.getValues());
            }
            return getQueryRunner().query(SQLParser.getSql(), newMapListHandler(), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> List<T> select(String select, Map<String, Object> params, Class<T> clazz) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(select), params);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().query(connection, SQLParser.getSql(), newBeanListHandler(clazz), SQLParser.getValues());
            }
            return getQueryRunner().query(
                    SQLParser.getSql(),
                    isPrimitive(clazz) ? newPrimitiveListHandler(clazz) : newBeanListHandler(clazz),
                    SQLParser.getValues()
            );
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public <T> List<T> list(T bean) { return select(bean); }
    @Override
    public <T> List<T> list(Class<T> clazz) { return select(clazz); }
    @Override
    public List<Map<String, Object>> list(String select) { return select(select); }
    @Override
    public List<Map<String, Object>> list(String select, Map<String, Object> params) { return select(select, params); }
    @Override
    public <T> List<T> list(String select, Map<String, Object> params, Class<T> clazz) { return select(select, params, clazz); }

    @Override
    public <T> Page<T> page(T bean, Pagination pagination) {
        try {
            Page<T> page = new Page<T>(pagination);
            SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.select(bean)), bean);
            IDialect dialect = getDialect();
            String query = dialect.query(SQLParser.getSql(), page);
            String count = dialect.count(SQLParser.getSql(), page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";" + toJson(bean));
            }
            List<T> items = getTransactionConnection() != null
                    ? getQueryRunner().query(getTransactionConnection(), query, newBeanListHandler((Class<T>)bean.getClass()), SQLParser.getValues())
                    : getQueryRunner().query(query, newBeanListHandler((Class<T>)bean.getClass()), SQLParser.getValues());
            if( !page.getHasMoreMode() ) {
                if( logger.isDebugEnabled() ) {
                    logger.debug(count + ";" + toJson(bean));
                }
                int total = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue()
                        : getQueryRunner().query(count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue();
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> Page<T> page(Class<T> clazz, Pagination pagination) {
        try {
            Page<T> page = new Page<T>(pagination);
            SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.select(clazz)), null);
            IDialect dialect = getDialect();
            String query = dialect.query(SQLParser.getSql(), page);
            String count = dialect.count(SQLParser.getSql(), page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";" + toJson(SQLParser.getValues()));
            }
            List<T> items = getTransactionConnection() != null
                    ? getQueryRunner().query(getTransactionConnection(), query, newBeanListHandler(clazz), SQLParser.getValues())
                    : getQueryRunner().query(query, newBeanListHandler(clazz), SQLParser.getValues());
            if( !page.getHasMoreMode() ) {
                if( logger.isDebugEnabled() ) {
                    logger.debug(count + ";" + toJson(SQLParser.getValues()));
                }
                int total = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue()
                        : getQueryRunner().query(count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue();
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public Page<Map<String, Object>> page(String select, Pagination pagination) {
        return page(select, (Map<String, Object>) null, pagination);
    }
    @Override
    public Page<Map<String, Object>> page(String select, Map<String, Object> params, Pagination pagination) {
        try {
            Page<Map<String, Object>> page = new Page<Map<String, Object>>(pagination);
            IDialect dialect = getDialect();
            SQLParser SQLParser = new SQLParser(onPrepareStatement(select), params);
            String query = dialect.query(SQLParser.getSql(), page);
            String count = dialect.count(SQLParser.getSql(), page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";" + params);
            }
            List<Map<String, Object>> items = getTransactionConnection() != null
                    ? getQueryRunner().query(getTransactionConnection(), query, newMapListHandler(), SQLParser.getValues())
                    : getQueryRunner().query(query, newMapListHandler(), SQLParser.getValues());
            if( !page.getHasMoreMode() ) {
                if( logger.isDebugEnabled() ) {
                    logger.debug(count + ";" + params);
                }
                int total = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue()
                        : getQueryRunner().query(count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue();
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> Page<T> page(String select, Map<String, Object> params, Class<T> clazz, Pagination pagination) {
        try {
            Page<T> page = new Page<T>(pagination);
            IDialect dialect = getDialect();
            SQLParser SQLParser = new SQLParser(onPrepareStatement(select), params);
            String query = dialect.query(SQLParser.getSql(), page);
            String count = dialect.count(SQLParser.getSql(), page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";" + params);
            }
            List<T> items = getTransactionConnection() != null
                    ? getQueryRunner().query(getTransactionConnection(), query, newBeanListHandler(clazz), SQLParser.getValues())
                    : getQueryRunner().query(query, newBeanListHandler(clazz), SQLParser.getValues());
            if( !page.getHasMoreMode() ) {
                if( logger.isDebugEnabled() ) {
                    logger.debug(count + ";" + params);
                }
                int total = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue()
                        : getQueryRunner().query(count, new ScalarHandler<Long>(1), SQLParser.getValues()).intValue();
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public <T>T get(T bean) {
        return get(bean, false);
    }
    @Override
    public <T> T get(T bean, boolean isUnique) {
        List<T> rets = select(bean);
        if( isUnique && rets.size() > 1 ) {
            throw new RTException("incorrect record count: expected 1, actual " + rets.size());
        }
        return rets.isEmpty() ? null : rets.get(0);
    }
    @Override
    public <T> T get(Class<T> clazz, Object id) {
        try {
            Assert.notNull(id, "id can't be null");

            String select = SQLBuilder.select(clazz, id);

            List<T> rets = null;
            if( isPrimitive(id) ) {
                if( logger.isDebugEnabled() ) {
                    logger.debug(select + ";" + id);
                }
                rets = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), onPrepareStatement(select), newBeanListHandler(clazz), id)
                        : getQueryRunner().query(onPrepareStatement(select), newBeanListHandler(clazz), id);
            }
            else if(id instanceof Map) {
                rets = select(select, (Map<String, Object>)id, clazz);
            }
            else {
                SQLParser SQLParser = new SQLParser(onPrepareStatement(select), id);
                if( logger.isDebugEnabled() ) {
                    logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
                }
                rets = getTransactionConnection() != null
                        ? getQueryRunner().query(getTransactionConnection(), SQLParser.getSql(), newBeanListHandler(clazz), SQLParser.getValues())
                        : getQueryRunner().query(SQLParser.getSql(), newBeanListHandler(clazz), SQLParser.getValues());
            }
            if( rets.size() > 1 ) {
                throw new RTException("incorrect record count: expected 1, actual " + rets.size());
            }
            return rets.isEmpty() ? null : rets.get(0);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> T get(String select, Map<String, Object> params, Class<T> clazz, boolean isUnique) {
        List<T> rets = select(select, params, clazz);
        if( isUnique && rets.size() > 1 ) {
            throw new RTException("incorrect record count: expected 1, actual " + rets.size());
        }
        return rets.isEmpty() ? null : rets.get(0);
    }

    @Override
    public <T> int insert(T bean) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.insert(bean)), bean);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Map<String, Object> pks = getTransactionConnection() != null
                    ? getQueryRunner().insert(getTransactionConnection(), SQLParser.getSql(), new MapHandler(), SQLParser.getValues())
                    : getQueryRunner().insert(SQLParser.getSql(), new MapHandler(), SQLParser.getValues());
            if( pks != null && !pks.isEmpty() ) {
                setPkAfterInsert(bean, pks);
            }
            return 1;
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    protected <T>void setPkAfterInsert(T bean, Map<String, Object> pks) throws IntrospectionException, IllegalAccessException {
        Assert.notNull(bean, "bean can't be null");
        Assert.notNull(pks, "pks can't be null");
        Metadata metadata = SQLBuilder.getMetadata(bean.getClass());
        for(String key : pks.keySet()) {
            Field field = metadata.getField(key);
            Object value = field.get(bean);
            if( isPrimitive(field.getType()) ){
                if( value == null ) {
                    field.set(bean, pks.get(key));
                }
            }
            else {
                throw new RTException("pk only support primitive type." + field.getName() + "," + field.getType());
                //复合主键
                /*
                if( value == null ) {
                    value = field.getType().newInstance();
                }
                setPkAfterInsert(value, pks);
                break;
                */
            }
        }
    }
    @Override
    public <T> int[] insert(List<T> beans) {
        try {
            Object[][] params = new Object[beans.size()][];
            String insert = null;
            int i = 0;
            for(T bean : beans) {
                SQLParser SQLParser = new SQLParser(onPrepareStatement(SQLBuilder.insert(bean)), bean);
                params[i] = SQLParser.getValues();
                insert = onPrepareStatement(SQLParser.getSql());
                i++;
            }
            if( logger.isDebugEnabled() ) {
                logger.debug(insert + ";" + toJson(params));
            }
            List<Map<String, Object>> pks = getTransactionConnection() != null
                    ? getQueryRunner().insertBatch(getTransactionConnection(), insert, new MapListHandler(), params)
                    : getQueryRunner().insertBatch(insert, new MapListHandler(), params);
            if( pks != null && !pks.isEmpty() && pks.size() == beans.size() ) {
                for(i=0; i<beans.size(); i++) {
                    setPkAfterInsert(beans.get(i), pks.get(i));
                }
            }
            int[] rets = new int[beans.size()];
            for(i=0; i<beans.size(); i++) {
                rets[i] = 1;
            }
            return rets;
            /*
            int[] rets = new int[beans.size()];
            for(int i=0; i<beans.size(); i++) {
                rets[i] = insert(beans.get(i));
            }
            return rets;
            */
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public <T> int delete(T bean) {
        try {
            String sql = onPrepareStatement(SQLBuilder.delete(bean));
            SQLParser SQLParser = new SQLParser(sql, bean);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().update(connection, SQLParser.getSql(), SQLParser.getValues());
            }
            return getQueryRunner().update(SQLParser.getSql(), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public <T> int update(T bean) {
        try {
            String sql = onPrepareStatement(SQLBuilder.update(bean));
            SQLParser SQLParser = new SQLParser(sql, bean);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().update(connection, SQLParser.getSql(), SQLParser.getValues());
            }
            return getQueryRunner().update(SQLParser.getSql(), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public int update(String sql) {
        try {
            sql = onPrepareStatement(sql);
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";");
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().update(connection, sql);
            }
            return getQueryRunner().update(sql);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public int update(String sql, Map<String, ?> params) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(sql), params);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().update(connection, SQLParser.getSql(), SQLParser.getValues());
            }
            return getQueryRunner().update(SQLParser.getSql(), SQLParser.getValues());
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public int[] update(String sql, Map<String, ?>[] batchValues) {
        try {
            sql = onPrepareStatement(sql);
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";");
            }
            String[] sqls = new String[batchValues.length];
            Object[][] params = new Object[batchValues.length][];
            int i = 0;
            for(Map<String, ?> values : batchValues) {
                SQLParser SQLParser = new SQLParser(sql, values);
                params[i] = SQLParser.getValues();
                sqls[i] = SQLParser.getSql();
                i++;
            }
            if( logger.isDebugEnabled() ) {
                logger.debug(sqls[0] + ";" + toJson(params));
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().batch(connection, sqls[0], params);
            }
            return getQueryRunner().batch(sqls[0], params);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public int execute(String sql) {
        try {
            sql = onPrepareStatement(sql);
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";");
            }
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                return getQueryRunner().execute(connection, sql);
            }
            return getQueryRunner().execute(sql);
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }

    @Override
    public int execute(String sql, Map<String, ?> params) {
        try {
            SQLParser SQLParser = new SQLParser(onPrepareStatement(sql), params);
            if( logger.isDebugEnabled() ) {
                logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
            }

            for(int i=0; i<SQLParser.getValues().length; i++) {
                if( SQLParser.getValues()[i] instanceof OutParameter ) {
                    OutParameter val = ((OutParameter)SQLParser.getValues()[i]);
                    SQLParser.getValues()[i] = new org.apache.commons.dbutils.OutParameter<Object>(val.getSqlType(), val.getJavaType());
                }
            }

            int rows = 0;
            Connection connection = getTransactionConnection();
            if( connection != null ) {
                rows = getQueryRunner().execute(connection, SQLParser.getSql(), SQLParser.getValues());
            }
            rows = getQueryRunner().execute(SQLParser.getSql(), SQLParser.getValues());

            for(int i=0; i<SQLParser.getValues().length; i++) {
                if( SQLParser.getValues()[i] instanceof org.apache.commons.dbutils.OutParameter ) {
                    String name = SQLParser.getNames()[i];
                    if( params.get(name) instanceof OutParameter ) {
                        ((OutParameter) params.get(name)).setValue(((org.apache.commons.dbutils.OutParameter)SQLParser.getValues()[i]).getValue());
                    }
                }
            }

            return rows;
        }
        catch (Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    public <T>T execute(IExecute<T> execute) {
        boolean closeConnection = false;
        Connection connection = null;
        try {
            connection = getTransactionConnection();
            if( connection == null ) {
                connection = getConnection();
                closeConnection = true;
            }
            return execute.execute(connection);
        }
        catch(Exception e) {
            if( closeConnection ) {
                DbUtils.closeQuietly(connection);
            }
            throw new RTException(e.getMessage(), e);
        }
    }

    SQLBuilder SQLBuilder = new SQLBuilder(){
        @Override
        protected Metadata newMetadata(Class<?> clazz) throws IntrospectionException {
            return new Metadata.RDSMetadata(clazz){
                @Override
                protected Connection getConnection() throws SQLException {
                    return Sdbc.this.getConnection();
                }
            }.initialize();
        }
    };
    protected Connection getConnection() throws SQLException {
        DataSource dataSource = getDataSource();
        Assert.notNull(dataSource, "NULL dataSource");
        return dataSource.getConnection();
    }
    protected DataSource getDataSource() {
        if( dataSource != null ) {
            return dataSource;
        }
        if( queryRunner != null ) {
            return queryRunner.getDataSource();
        }
        return null;
    }
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.queryRunner = new QueryRunner(dataSource);
    }
    public QueryRunner getQueryRunner() {
        if( queryRunner == null ) {
            synchronized (this) {
                if( queryRunner == null ) {
                    queryRunner = new QueryRunner(this.dataSource);
                }
            }
        }
        return queryRunner;
    }
    public void setQueryRunner(QueryRunner queryRunner){
        this.queryRunner = queryRunner;
        if( queryRunner != null ) {
            this.dataSource = queryRunner.getDataSource();
        }
    }
    private boolean isPrimitive(Object o) {
        return SQLBuilder.isPrimitive(o);
    }
    private boolean isPrimitive(Class<?> clazz) {
        return SQLBuilder.isPrimitive(clazz);
    }

    private String toJson(Object object) {
        return new GsonBuilder()
                .excludeFieldsWithModifiers(Modifier.FINAL, Modifier.TRANSIENT, Modifier.STATIC)
                .serializeNulls()
                .create()
                .toJson(object);
    }

    private <T>ResultSetHandler<List<T>> newPrimitiveListHandler(Class<T> clazz) {
        return new ResultSetHandler<List<T>>() {
            @Override
            public List<T> handle(ResultSet rs) throws SQLException {

                ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();
                if( cols > 1 ) {
                    throw new RTException("Incorrect column count: expected 1, actual " + cols);
                }
                List<T> rets = new ArrayList<>();
                while( rs.next() ) {
                    Object val = null;
                    if( Integer.class.isAssignableFrom(clazz) ) {
                        val = rs.getInt(1);
                    }
                    else if( Long.class.isAssignableFrom(clazz) ) {
                        val = rs.getLong(1);
                    }
                    else if( Short.class.isAssignableFrom(clazz) ) {
                        val = rs.getShort(1);
                    }
                    else if( Float.class.isAssignableFrom(clazz) ) {
                        val = rs.getFloat(1);
                    }
                    else if( Double.class.isAssignableFrom(clazz) ) {
                        val = rs.getDouble(1);
                    }
                    else if( Boolean.class.isAssignableFrom(clazz) ) {
                        val = rs.getBoolean(1);
                    }
                    else if( Byte.class.isAssignableFrom(clazz) ) {
                        val = rs.getByte(1);
                    }
                    else if( String.class.isAssignableFrom(clazz) ) {
                        val = rs.getString(1);
                    }
                    else {
                        val = rs.getObject(1);
                    }
                    rets.add((T) val);
                }
                return rets;
            }
        };
    }
    private <T>BeanListHandler<T> newBeanListHandler(Class<T> clazz) {
        return new BeanListHandler<T>(clazz, new BasicRowProcessor(new GenerousBeanProcessor(clazz)));
    }
    public MapListHandler newMapListHandler() {
        return new MapListHandler(new BasicRowProcessor());
    }
    public class GenerousBeanProcessor extends org.apache.commons.dbutils.GenerousBeanProcessor {

        private Class<?> clazz;

        public GenerousBeanProcessor(Class<?> clazz) {
            this.clazz = clazz;
        }

        @Override
        protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException {

            int cols = rsmd.getColumnCount();
            int[] columnToProperty = new int[cols + 1];
            Arrays.fill(columnToProperty, -1);

            for(int col = 1; col <= cols; ++col) {
                String columnName = rsmd.getColumnLabel(col);
                if (null == columnName || 0 == columnName.length()) {
                    columnName = rsmd.getColumnName(col);
                }

                String generousColumnName = columnName.replace("_", "");

                for(int i = 0; i < props.length; ++i) {
                    String propName = props[i].getName();
                    try {
                        Field field = this.clazz.getDeclaredField(propName);
                        Column column = field.getAnnotation(Column.class);
                        if( column != null ) {
                            propName = column.name();
                        }
                    }
                    catch(Exception e) {
                        logger.warn(e.getMessage(), e);
                    }
                    if (columnName.equalsIgnoreCase(propName) || generousColumnName.equalsIgnoreCase(propName)) {
                        columnToProperty[col] = i;
                        break;
                    }
                }
            }
            return columnToProperty;
        }
    }
}
